SQL Serverのテーブル,オブジェクトの実現方法としてのデータページ構成~行ストアと列ストア~
引用元
https://gyazo.com/53671da65807b29e83d83d54d3176b56
データベースの実体はデータファイルであるが, データベース内のテーブル/オブジェクトは以下にデータファイルで実現されているのか. 保守性(管理や実用性の高さ)から行ストア型, 大規模処理への相性から列ストア型がそれぞれ存在する.
関連: SQL Serverのデータベース実体をファイル,Diskの中身から探る
公式: SQL Server のインデックスのアーキテクチャとデザイン ガイド https://docs.microsoft.com/ja-jp/sql/relational-databases/sql-server-index-design-guide?view=sql-server-ver15
公式: トランザクションのロックおよび行のバージョン管理ガイド https://docs.microsoft.com/ja-jp/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-ver15
--.icon
ページの内部構成の詳細~行ストア型編~
ページは①データページ, ②インデックスページに大きく分類される.
①データページは, 更にヒープページ, クラスタ化インデックスのデータページに分類され, ②インデックスページはクラスタ化インデックスと非クラスタ化インデックスに分類される.
インデックスページ
探索を効率化するために, 特定データ(リーフノード)までのルート(ファイルシステムのイメージ)への探索用キーの割り振りを担う.
https://gyazo.com/434a2c2a3e27bd42ad634d6af8199454
クラスタ化インデックスと非クラスタ化インデックスの違い
リーフノードに実データをもっていればクラスタ化インデックス, 実データが分離されていれば非クラスタ化インデックスとなる. クラスタ化インデックスは物理ID順に陳列されていないし, テーブル1つに対してしか設定ができない. 故にキー順の参照や探索に向いている. 例: 100~199までを取得する など. 一方で非クラスタ化インデックスはそもそもリーフノードを999個までしか保持ができない. 与えられたキーをベースに小規模な探索に向いていると言える. 例: 集計区分値を用いた探索など.
https://gyazo.com/6f532c4f5986deb54363725a43e35307
インデックスのメリットとデメリット
データ探索時に多くのインデックス参照をできたほうが, データページまでのアクセスが不要となり, リーフノードまでのアクセスで済むので参照は効率的となる(メリット, カバリングインデックスと呼ばれる). 一方でインデックスに項目を追加するほどインデックスページが保持すべきデータが増えることになる(デメリット). この両方をいいとこ取りしたのが, 付加列インデックスであり, 非クラスタ化インデックのリーフノードに対して任意の列を仮想インデックスとして追加することで, データページへのアクセスを減らして探索効率の向上を実現した.
https://gyazo.com/dea0411e9abd1a0d01bd986473135b8a
https://www.slideshare.net/hamu502/sql-server-102391836
データページ
テーブル行を構成する列データを保持. 要はテーブルの1行1行をファイル内に格納している. なお, ヘッダー情報はインデックスページのそれと近い.
ヒープページとは ... クラスタ化インデックスが定義されていないデータページ. つまり物理IDによって識別定義, 並び替えがなされておらず, 物理的なデータの順序に脈絡のないデータページ. 非クラスタ化インデックスが定義されていたとしても物理順序は整列されていない.
列ストア型
列ストアの登場
行ストア型のデータ管理だと問題があった. 大規模データ処理等を必要とするデータウェアハウスなどにおいて, 分析/行抽出実施時に, 分析対象外の列まで抽出/メモリ展開するというオーバーヘッドが発生してしまっていた. 加えて, 列において重複データが発生しており圧縮可能な対象であった. そこで列単位でデータを格納して管理する列ストア型のデータ管理が台頭した.
行ストアはOLTPといったオンラインのトランザクション処理に向いている, 一方で集計や大規模分析には向いていない(そもそもデータモデルを正規化しているのでテーブル間のJOINも大変)ので, 列ストアが適している. 状況に応じて使い分けられると効率的, 効果的である. 以上2つを組み合わせたデータ管理アーキテクチャソリューションとして, HTAP(Hybrid Transaction / Analysis Processing)がある.
列ストア圧縮には①データ分割, ②エンコード, ③データ圧縮のプロセスを経る.
①データ分割では行データの重複部分をグルーピング化して圧縮する方針である. RBDだとオブジェクト(行)に対して正規化を実施するが, こちらは列に対してある種正規化(グルーピングという抽象化)を処理しているといえる.
②エンコードでは, 分析, 演算効率を高めるために文字列型やカーディナリの低い数値型はエンコードしてディクショナリ登録する(区分値を設け, マスターデータ化してるイメージ)
https://gyazo.com/75a5d7d96bc565a5399e7958112310c5
https://enterprisezine.jp/article/detail/8553
列ストアにもインデックスの概念がある.
クラスタ化列ストアインデックスは, 列ストア内のデータがインデックスとして管理される. 中間ノードに列ストア内のデータが管理されているイメージ. コレ自体がキーになる.
非クラスタ化列ストアインデックスは, ヒープやクラスタ化インデックスといった行ストア型で格納されているテーブルに, 列ストアインデックス情報を付け加えるイメージ. コレ自体はキーとならずに, 探索の補足情報となる.
https://gyazo.com/6e9f95d4a2819d831a13e63009966c31
https://www.slideshare.net/odashinsuke/sql-server-73269307
https://gyazo.com/7e0dd081dbce8b4af52827ef3d658480
https://docs.microsoft.com/ja-jp/archive/blogs/sqlserverstorageengine/columnstore-index-differences-between-clusterednonclustered-columnstore-index
実際のファイル中身を参照してみる.
DBCC PAGE/INDコマンドを利用して, 実際のデータないしインデックスファイルへアクセスすることが可能. P123より確認してください.
sys.dm_db_page_infoの動的管理ビューでの確認も可能.
#Azure #Database #SQLServer